#!/usr/bin/env python
# -*- coding: utf-8 -*-

import csv
import cx_Oracle as cx
from src.manager.mongodb_manager import MongodbManager as MongodbManager
from src.config.mongodb_config import MongodbConfig as MongodbConfig


class StockInfoUtil:
    # 文件路径
    File = 'C:/mywork/temp/stock_info.csv'

    def __init__(self):
        """
        初始化数据库连接和session
        """
        # 连接数据库，创建session
        super().__init__()
        self.connection = cx.connect('scott', 'tiger', '127.0.0.1:1521/adam')
        self.cursor = self.connection.cursor()

    def update_board_id(self):
        """
        更新stock_info表的board_id字段
        """
        with open(StockInfoUtil.File, encoding='utf-8') as f:
            f_csv = csv.reader(f)
            # 解析行
            for row_index, row in enumerate(f_csv):
                print(row_index, '--------------------------------')

                # 跳过标题行
                if row_index == 0:
                    continue

                # 股票代码
                stock_code = str()
                # 板块名称
                board_name = str()

                # 解析列
                for column_index, column in enumerate(row):
                    if column_index == 1:
                        stock_code = column
                        print(column_index, column)
                    if column_index == 3:
                        board_name = column
                        print(column_index, column)

                # 更新stock_info表的board_id列
                sql_select_board_id = "select t.id from board t where t.name='" + board_name + "'"
                self.cursor.execute(sql_select_board_id)
                for board_id_tuple in self.cursor.fetchall():
                    sql_update_board_id = "update stock_info set board_id=" + str(
                        board_id_tuple[0]) + " where code_='" + str(stock_code) + "'"
                    self.cursor.execute(sql_update_board_id)
                    self.connection.commit()

    def update_id_in_board(self):
        """
        更新board表的id字段
        """
        with open(StockInfoUtil.File, encoding='utf-8') as f:
            f_csv = csv.reader(f)
            # 解析行
            for row_index, row in enumerate(f_csv):
                print(row_index, '--------------------------------')

                # 跳过标题行
                if row_index == 0:
                    continue

                # 股票代码
                stock_code = str()
                # 股票名称
                stock_name = str()
                # 板块名称
                board_name = str()

                # 解析列
                for column_index, column in enumerate(row):
                    if column_index == 0:
                        stock_code = column[2:]
                        print(column_index, column)
                    if column_index == 1:
                        stock_name = column
                        print(column_index, column)
                    if column_index == 2:
                        board_name = column
                        print(column_index, column)

                # 根据股票名称（stock_name）和股票所属板块（board_name），更新board表的id字段
                sql_select_board_id = "select t.board_id from stock_info t where t.name_='" + stock_name + "'"
                self.cursor.execute(sql_select_board_id)
                try:
                    for board_id_tuple in self.cursor.fetchall():
                        sql_update_board_id = "update board t set t.id=" + str(
                            board_id_tuple[0]) + " where t.name='" + str(board_name) + "'"
                        self.cursor.execute(sql_update_board_id)
                        self.connection.commit()
                except:
                    print('出现重复')

    def update_url_param(self):
        """
        更新stock_info表的url_param字段
        先从mongodb的black-widow数据库的stock_info集合中取出数据，在与oracle的ADAM数据库的stock_info表的code_字段做比较，
        将相等的插入到stock_info表的url_param字段中
        注意：oracle的stock_info表中的记录数可能跟mongodb的stock_info表中的记录数不一样，还需要手工修改
        :return:
        """
        mongodb_manager = MongodbManager(MongodbConfig.Host, MongodbConfig.Port, MongodbConfig.Database)
        stock_info_cursor = mongodb_manager.get_collection(MongodbConfig.Stock_Info_Collection).find()
        for stock_info in stock_info_cursor:
            code = stock_info['code']
            url_param = stock_info['url_param']
            sql_update_url_param = "update stock_info set url_param='" + str(url_param) + "' where code_='" + str(
                code) + "'"
            self.cursor.execute(sql_update_url_param)
            self.connection.commit()
        pass

    def update_mark_in_stock_info(self):
        """
        更新stock_info表的mark字段
        """
        with open(StockInfoUtil.File, encoding='utf-8') as f:
            f_csv = csv.reader(f)
            # 解析行
            row_set = set()
            for row_index, row in enumerate(f_csv):
                # 跳过标题行
                if row_index == 0:
                    continue

                # 解析列
                # 表示这只股票是否可以融资融券
                is_r = False
                for column_index, column in enumerate(row):
                    # 如果第一列是4，则表示是融资融券，那么就更新stock_info表的mark字段
                    if column_index == 0 and column == "3":
                        is_r = True
                        continue
                    if column_index == 1 and is_r == True:
                        stock_code = column[2:]
                        # 更新stock_info表的mark列
                        sql_select_board_id = "update stock_info set mark='R' where code_='" + column + "'"
                        self.cursor.execute(sql_select_board_id)
                        self.connection.commit()
                        is_r = False
                        break

    def update_column_and_url_param(self):
        """
        更新code和url_param字段
        """

        sql = "update stock_info t set t.url_param=upper(t.code_)"
        self.cursor.execute(sql)
        sql = "update stock_info t set t.code_=substr(t.code_, 3, 6)"
        self.cursor.execute(sql)
        sql = "update stock_info t set t.url_param=lower(substr(t.url_param, 0, 2)) || '.' ||substr(t.url_param, 3)"
        self.cursor.execute(sql)
        self.connection.commit()


if __name__ == '__main__':
    stock_info_util = StockInfoUtil()
    # stock_info_util.update_board_id()
    # stock_info_util.update_id_in_board()
    # stock_info_util.update_url_param()
    # stock_info_util.update_mark_in_stock_info()
    stock_info_util.update_column_and_url_param()
